package com.example.teacherservice.repository;

import com.example.teacherservice.entity.StuCourse;
import com.example.teacherservice.entity.StuCourseAppraise;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Optional;

public interface StuCourseRepository extends JpaRepository<StuCourse, Long> {
    @Query("SELECT sc.courseId FROM StuCourse sc WHERE sc.stuId = :studentId")
    List<Long> findCourseIdsByStudentId(@Param("studentId") Long studentId);

    List<StuCourse> findByStuId(Long studentId);

    @Query("SELECT s.id FROM Student s WHERE s.userId = :userId")
    Optional<Long> findStudentIdByUserId(@Param("userId") Long userId);

    boolean existsByStuIdAndCourseId(Long stuId, Long courseId);

    boolean existsByStudentIdAndCourseId(Long studentId, Long courseId);

    // 删除班级-学生关联
    @Modifying
    @Query("DELETE FROM StuClass sc WHERE sc.classId = :classId")
    void deleteByClassId(@Param("classId") Long classId);

    // 查询班级学生数量
    @Query("SELECT COUNT(sc) FROM StuClass sc WHERE sc.classId = :classId")
    int countByClassId(@Param("classId") Long classId);

    // 根据课程ID查询选课记录
    List<StuCourse> findByCourseId(Long courseId);

    // 查询学生的选课记录（带课程信息）
    @Query("SELECT sc FROM StuCourse sc " +
            "JOIN FETCH sc.course c " +
            "WHERE sc.stuId = :stuId AND c.deleted = 0")
    List<StuCourse> findByStuId1(@Param("stuId") Long stuId);

    @Modifying
    @Query("DELETE FROM StuCourse sc WHERE sc.stuId = :studentId AND sc.courseId = :courseId")
    void deleteByStuIdAndCourseId(@Param("studentId") Long studentId, @Param("courseId") Long courseId);

    @Query("SELECT sc.stuId FROM StuCourse sc WHERE sc.courseId = :courseId AND sc.stuId IN :studentIds")
    List<Long> findStudentIdsByCourseIdAndStudentIds(
            @Param("courseId") Long courseId,
            @Param("studentIds") List<Long> studentIds);

    @Modifying
    @Transactional
    @Query(value = "INSERT INTO stu_course (student_id, course_id, create_time, update_time) " +
            "VALUES (?1, ?2, NOW(), NOW()) ON DUPLICATE KEY UPDATE update_time = NOW()",
            nativeQuery = true)
    int addCourseToStudent(Long studentId, Long courseId);

    @Modifying
    @Transactional
    @Query("DELETE FROM StuCourse sc WHERE sc.courseId = :courseId")
    void deleteByCourseId(@Param("courseId") Long courseId);

}

